/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.telsoft.security.data;

import com.telsoft.security.entity.SEC_ETT_Group;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author nguyenthang
 */
public class SEC_DATA_Group {
    
    private Connection connect = null;
    SEC_ETT_Group group;
    
    public SEC_DATA_Group() {
    }
    
    public SEC_ETT_Group getAllOfGroupByID(int GROUP_ID) {
        SEC_ETT_Group returnValue = new SEC_ETT_Group();
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        ResultSet rs;
        try {
            connect = connectDB.dbConnect();
            String query = "SELECT GROUP_NAME, DESCRIPTION, STATUS FROM SEC_GROUP WHERE GROUP_ID = '" + GROUP_ID + "'";
            stmt = connect.prepareStatement(query);
            rs = stmt.executeQuery();
            while (rs.next()) {
                returnValue.setGROUP_ID(GROUP_ID);
                returnValue.setGROUP_NAME(rs.getString("GROUP_NAME"));
                returnValue.setDESCRIPTION(rs.getString("DESCRIPTION"));
                returnValue.setSTATUS(rs.getInt("STATUS"));
            }
            connectDB.dbClose(connect, stmt, rs);
        } catch (Exception ex) {
            ex.toString();
        }
        return returnValue;
    }
    
    public List<SEC_ETT_Group> getAllGroup() {
        List<SEC_ETT_Group> returnValue = new ArrayList<SEC_ETT_Group>();
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        ResultSet rs;
        try {
            connect = connectDB.dbConnect();
            String query = "SELECT * FROM SEC_GROUP ORDER BY GROUP_ID ASC";
            stmt = connect.prepareStatement(query);
            rs = stmt.executeQuery();
            while (rs.next()) {
                group = new SEC_ETT_Group();
                group.setGROUP_ID(rs.getInt("GROUP_ID"));
                group.setGROUP_NAME(rs.getString("GROUP_NAME"));
                group.setDESCRIPTION(rs.getString("DESCRIPTION"));
                group.setSTATUS(rs.getInt("STATUS"));
                
                returnValue.add(group);
            }
            connectDB.dbClose(connect, stmt, rs);
        } catch (Exception ex) {
            ex.toString();
        }
        return returnValue;
    }
    
    public SEC_ETT_Group getGroupByName(String GrName){
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        SEC_ETT_Group gr = new SEC_ETT_Group();
        ResultSet rs;
        try {
            connect = connectDB.dbConnect();
            String query = "SELECT * FROM SEC_GROUP WHERE GROUP_NAME = ?";
            stmt = connect.prepareStatement(query);
            stmt.setString(1, GrName);
            rs = stmt.executeQuery();
            while (rs.next()){
                gr.setGROUP_ID(rs.getInt("GROUP_ID"));
                gr.setGROUP_NAME(rs.getString("GROUP_NAME"));
                gr.setSTATUS(rs.getInt("STATUS"));
                gr.setDESCRIPTION(rs.getString("DESCRIPTION"));
            }
            connectDB.dbClose(connect, stmt, rs);
        } catch (Exception ex){
            ex.toString();
        }
        return gr;
    }
    
    public void deleteGroupByID(int groupID){
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt; 
        try {
            connect = connectDB.dbConnect();
            String query = "DELETE FROM SEC_GROUP WHERE GROUP_ID = ?";
            stmt = connect.prepareStatement(query);
            stmt.setInt(1, groupID);
            stmt.execute();
            connectDB.dbClose(connect);
            connectDB.dbClose(stmt);
        } catch (Exception ex){
            ex.toString();
        }
    }
    
    public void addGroup(SEC_ETT_Group newGroup){
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        try {
            connect = connectDB.dbConnect();
            String query = "INSERT INTO SEC_GROUP(GROUP_NAME, DESCRIPTION, STATUS) VALUES (?,?,?)";
            stmt = connect.prepareStatement(query);
            stmt.setString(1, newGroup.getGROUP_NAME());
            stmt.setString(2, newGroup.getDESCRIPTION());
            stmt.setInt(3, newGroup.getSTATUS());
            stmt.execute();
            connectDB.dbClose(connect);
            connectDB.dbClose(stmt);
        } catch (Exception ex){
            ex.toString();
        }
    }
}
